package com.mjduan.myBank.util.excelUtil;

import java.io.IOException;
import java.io.InputStream;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.LinkedList;
import java.util.List;

import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class ExcelUtil {
	/**
	 * 有3列，第一列为金额、第二列为备注、第三列为时间
	 */
	private static final int COLUMNS=3;
	
	private ExcelUtil(){
		
	}
	
	/**
	 * 会检查数据格式是否合法
	 * 
	 * @param inputStream
	 * @param sheetIndex
	 * @param title
	 * @return
	 * @throws ExcelDataFormatException
	 */
	public static List<Object[]> readDataFromExcelWithPoi(
			InputStream inputStream,int sheetIndex,String[] title) throws ExcelDataFormatException{
		if((inputStream == null)||(sheetIndex<0)||(title==null)){
			return new LinkedList<Object[]>();
		}
		List<Object[]> list = new LinkedList<Object[]>();
		Object[] obj=null;
		XSSFWorkbook workbook = null;
		try {
			 workbook = new XSSFWorkbook(inputStream);
			//获取第一个工作表sheet
			XSSFSheet sheet = workbook.getSheetAt(sheetIndex);
			//i从1开始取值，这是跳过了第一行，第一行时标题
			for (int i = 1; i <= sheet.getLastRowNum(); i++) {
				obj = new Object[COLUMNS];
				XSSFRow row = sheet.getRow(i);
				//读取当前行的最后一列的列号
				int lastCellNum = row.getLastCellNum();
				if(lastCellNum<(COLUMNS-1)){
					throw new ExcelDataFormatException("金额和备注不能为空，添加失败!");
				}else if(lastCellNum>COLUMNS){
					throw new ExcelDataFormatException("添加失败，只能是三列(第一列是金额、"
							+ "第二列是说明、第三列是时间)");
				}
				
				for (int j = 0; j < lastCellNum; j++) {
					XSSFCell cell = row.getCell(j);
					
					if(cell==null){
						throw new ExcelDataFormatException("金额和备注不能为空，添加失败!");
					}
					
					switch (cell.getCellType()) {
					case XSSFCell.CELL_TYPE_NUMERIC:
						short format = cell.getCellStyle().getDataFormat();
						if(format==14){
							//这是日期
							double value = cell.getNumericCellValue();
							Date date = DateUtil.getJavaDate(value);
							SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
							String result = simpleDateFormat.format(date);
                            //由于我们在输入时间时，只是精确到日，没有精确到时、分，所以我们假设是21点整输入的的
							obj[j] = result+" 21-00";
						}else{
							//这是金额
							obj[j]=cell.getNumericCellValue();
						}
						
						break;
					case XSSFCell.CELL_TYPE_STRING:
						obj[j]=cell.getStringCellValue();
						break;
					case XSSFCell.CELL_TYPE_BLANK:
						obj[j]="";
						break;
						//switch在任何时候都应该有个default结尾
					default: 
						obj[j]="";
						break;
					} 
				}
				list.add(obj);
			}
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			try {
				if(workbook!=null){
					workbook.close();
				}
				if(inputStream!=null){
					inputStream.close();
				}
			} catch (IOException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}

		return list;
	}
	
	

}
